// <copyright project="Salient.SqlServer" file="Database.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.   
// Attribution is appreciated
// </copyright> 
// <version>1.0</version>
using System.Collections.Specialized;
using System.Data.SqlClient;

namespace Salient.SqlServer.Management
{
    public class Database : SqlCmdRunner
    {
        private const string CreateDb =
            @"
  USE [master]
  GO
  
  CREATE DATABASE [{0}]
  IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  begin
  	EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'enable'
  end
  ALTER DATABASE [{0}] SET ANSI_NULL_DEFAULT OFF 
  ALTER DATABASE [{0}] SET ANSI_NULLS OFF 
  ALTER DATABASE [{0}] SET ANSI_PADDING OFF 
  ALTER DATABASE [{0}] SET ANSI_WARNINGS OFF 
  ALTER DATABASE [{0}] SET ARITHABORT OFF 
  ALTER DATABASE [{0}] SET AUTO_CLOSE OFF 
  ALTER DATABASE [{0}] SET AUTO_CREATE_STATISTICS ON 
  ALTER DATABASE [{0}] SET AUTO_SHRINK OFF 
  ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS ON 
  ALTER DATABASE [{0}] SET CURSOR_CLOSE_ON_COMMIT OFF 
  ALTER DATABASE [{0}] SET CURSOR_DEFAULT  GLOBAL 
  ALTER DATABASE [{0}] SET CONCAT_NULL_YIELDS_NULL OFF 
  ALTER DATABASE [{0}] SET NUMERIC_ROUNDABORT OFF 
  ALTER DATABASE [{0}] SET QUOTED_IDENTIFIER OFF 
  ALTER DATABASE [{0}] SET RECURSIVE_TRIGGERS OFF 
  ALTER DATABASE [{0}] SET  DISABLE_BROKER 
  ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
  ALTER DATABASE [{0}] SET DATE_CORRELATION_OPTIMIZATION OFF 
  ALTER DATABASE [{0}] SET TRUSTWORTHY OFF 
  ALTER DATABASE [{0}] SET ALLOW_SNAPSHOT_ISOLATION OFF 
  ALTER DATABASE [{0}] SET PARAMETERIZATION SIMPLE 
  ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT OFF 
  ALTER DATABASE [{0}] SET  READ_WRITE 
  ALTER DATABASE [{0}] SET RECOVERY FULL 
  ALTER DATABASE [{0}] SET  MULTI_USER 
  ALTER DATABASE [{0}] SET PAGE_VERIFY CHECKSUM  
  ALTER DATABASE [{0}] SET DB_CHAINING OFF
  
  PRINT('Database [{0}] created...')
  
  GO
  ";

        private const string DropDb =
            @"
USE [master]
GO

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}') 
begin
	ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE {0}
	PRINT('Database [{0}] dropped for recreation...')
end

GO
";

        private const string UseDb = "USE {0}\r\nGO";


        public Database(string dataSource, string initialCatalog)
        {
            DataSource = dataSource;
            InitialCatalog = initialCatalog;
            IntegratedSecurity = true;
        }

        public Database(string dataSource, string initialCatalog, string userId, string password)
        {
            DataSource = dataSource;
            InitialCatalog = initialCatalog;
            UserID = userId;
            Password = password;
        }

        public Database(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public void DropAndCreate()
        {
            DropAndCreate(new StringCollection());
        }

        public void DropAndCreate(StringCollection scripts)
        {
            Drop();
            Create(scripts);
        }

        public void Drop()
        {
            var query = new StringCollection
                            {
                                string.Format(DropDb,
                                              InitialCatalog)
                            };

            var runner = new SqlCmdRunner
                             {
                                 ConnectionString =
                                     new SqlConnectionStringBuilder(ConnectionString) {InitialCatalog = "Master"}.
                                     ConnectionString
                             };

            runner.ExecuteScript(query);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="scripts">Batches to run after database creation. Do NOT include CREATE DATABASE batch. It is included by this method.</param>
        public void Create(StringCollection scripts)
        {
            scripts.Insert(0,
                           string.Format(UseDb,
                                         InitialCatalog));
            scripts.Insert(0,
                           string.Format(CreateDb,
                                         InitialCatalog));

            var runner = new SqlCmdRunner
                             {
                                 ConnectionString =
                                     new SqlConnectionStringBuilder(ConnectionString) {InitialCatalog = "Master"}.
                                     ConnectionString
                             };
            runner.ExecuteScript(scripts);
        }

        public void Create()
        {
            Create(new StringCollection());
        }
    }
}